<?php
namespace frontend\controllers;

use Yii;
use PDO;
use yii\web\Controller;

class ExportController extends Controller
{

	public $layout = false;

	public $enableCsrfValidation = false;

	public function pdo(){

		$k = Yii::$app->session->get('link_k'); //k值
		$dbname = Yii::$app->session->get('dbname'); //当前数据表所属的数据库名称
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        $one_dbinfo = $dbinfos_arr[$k];
        $dbh = new PDO("mysql:host=$one_dbinfo[1];port=$one_dbinfo[2];dbname=$dbname;charset=utf8", "$one_dbinfo[3]", "$one_dbinfo[4]");
        return $dbh;
	}

	public function actionSql(){
		
		$table = Yii::$app->session->get('table');
		if(empty($table)){
			$len = count($table);
		}else{
			$len = 1;
		}
		
		$this->header($table, $len);

		// print_r($desc_arr);die;
	}

	public function actionWork(){
		$table = Yii::$app->session->get('table');
		$dbh = $this->pdo();
		$sql = "select * from `$table`";
		$arr = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
		
		$header = '<html><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"></head><body style="tab-interval:21.0pt"><table border=1 cellspacing=0 cellpadding=0 style="border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt;
 mso-yfti-tbllook:1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt">';
 $footer = '</table></body></html>';
 		$htmlStr = '';
		foreach ($arr as $k => $val) {
			$htmlStr .= '<tr>';
			// $htmlStr = "INSERT INTO `".$value."` VALUES (";
		    foreach($val as $zd){
		    	$htmlStr .= '<td style="width:138.25pt;border:solid windowtext 1.0pt;border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt"><p style="text-align:center">'.$zd.'</p></td>';
		    }
		    //去掉最后一个逗号和空格
		    $htmlStr .= '</tr>';
		}
		//文件下载
		$this->download($table.'.doc', $header.$htmlStr.$footer);


	}

	public function header($table, $len){

		$dbh = $this->pdo();
		if($len != 1){
			$sql = "show tables from `a`";
			$tabList = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
			$len = count($tabList);
		}else{
			$tabList = [$table];
		}

		foreach ($tabList as $key => $value) {
			$sql = "show create table `$value`";
			$arr = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
			// print_r($arr);die;
			$info = "-- ----------------------------\r\n";
			$info .= "-- 日期：".date("Y-m-d H:i:s",time())."\r\n";
			$info .= "-- 仅用于测试和学习,本程序不适合处理超大量数据\r\n";
			$info .= "-- ----------------------------\r\n\r\n";
			$sqlStr = $info.$arr[0]['Create Table'].";\r\n\r\n";

		}

		foreach ($tabList as $key => $value) {
			$sql = "select * from `$value`";
			$arr = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
			$arr_len = count($arr);
			if($arr_len<1) continue;
			$sqlStr .= "-- ----------------------------\r\n";
			$sqlStr .= "-- Records for `".$value."`\r\n";
			$sqlStr .= "-- ----------------------------\r\n";
			foreach ($arr as $k => $val) {
				$sqlStr .= "INSERT INTO `$value` VALUES (";
				// $sqlStr = "INSERT INTO `".$value."` VALUES (";
			    foreach($val as $zd){
			    	$sqlStr .= "'".$zd."', ";
			    }
			    //去掉最后一个逗号和空格
			    $sqlStr = substr($sqlStr,0,strlen($sqlStr)-2);
			    $sqlStr .= ");\r\n";
			}

			//文件下载
			$this->download($value.'.sql', $sqlStr);
			// print_r($sqlStr);die;
		}


	}

	public function download($showname, $content) {
 	
  		if(strstr($_SERVER['HTTP_USER_AGENT'], "MSIE")) {
	        $showname = rawurlencode($showname);
	        $showname = preg_replace('/\./', '%2e', $showname, substr_count($showname, '.') - 1);
	    }
	 
	    header("Cache-Control: "); 
	    header("Pragma: "); 
	    header("Content-Type: application/octet-stream"); 
	    header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
	 
	    header("Content-Length: " .(string)(strlen($content))); 
	    header('Content-Disposition: attachment; filename="'.$showname.'"'); 
	    header("Content-Transfer-Encoding: binary\n"); 
	 
	    echo $content;
	 
	    exit();
	}
}